package com.fantuan.db.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.springframework.stereotype.Service;

import com.fantuan.db.bean.Group;
import com.fantuan.db.bean.User;

@Service
public class UserDAO extends BaseDAO{

	public User getUser(int nid) throws SQLException {

		Connection conn = dataSource.getConnection();

		String sql = "select * from t_usercenter where `nid`=?";

		User result = null;
		try {

			Object[] objs = new Object[] { nid };

			result = (User) this.query(conn, sql, new BeanHandler<User>(
					User.class), objs);

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtils.close(conn);
		}

		return result;
	}
	
	
	
	public User getUser(String email, String password) throws SQLException {

		Connection conn = dataSource.getConnection();

		String sql = "select * from t_usercenter where `sEMail`=? and `sPassword`=?";

		User result = null;
		try {

			Object[] objs = new Object[] { email,password };

			result = (User) this.query(conn, sql, new BeanHandler<User>(
					User.class), objs);

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtils.close(conn);
		}

		return result;
	}
	
	
	
	public User getUserByEmail(String email) throws SQLException {

		Connection conn = dataSource.getConnection();

		String sql = "select * from t_usercenter where `sEMail`=?";

		User result = null;
		try {

			Object[] objs = new Object[] { email };

			result = (User) this.query(conn, sql, new BeanHandler<User>(
					User.class), objs);

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtils.close(conn);
		}

		return result;
	}

	
	
	public List<User> getUserListByGroupID(int groupid) throws SQLException {

		Connection conn = dataSource.getConnection();

		String sql = "select a.* from t_usercenter a,t_yw_ftusermoney b where a.nid=b.nUserID and b.nGroupID=? order by b.nUserID";

		List<User> result = null;
		try {

			Object[] objs = new Object[] { groupid };

			result = (List<User>) this.query(conn, sql, new BeanListHandler<User>(
					User.class), objs);

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtils.close(conn);
		}

		return result;
	}
	
	
	
	/**
	 * 新增用户
	 * 
	 * @param group
	 * @return
	 * @throws SQLException
	 */
	public long addUser(User user) throws SQLException {

		String sql = "insert into t_usercenter(`sEMail`,"
				+ "`sNickName`,`sPassword`,`dRegDate`,"
				+ "`nState`) values(?,?,?,now(),?)";

		long id = -1;
		Connection conn = null;
		try {

			conn = dataSource.getConnection();

			Object[] objs = new Object[] { user.getsEMail(),user.getsNickName(),user.getsPassword(),user.getnState() };
			this.update(conn, sql, objs);

			id = this.getLastInsertID(conn);

			DbUtils.close(conn);
		} catch (SQLException e) {
			DbUtils.close(conn);
			e.printStackTrace();
		}

		return id;
	}
	
	
	
	public void updateUser(User user) throws SQLException {

		String sql = "update t_usercenter set `sEMail`=?,"
				+ "`sNickName`=?,`sPassword`=?,"
				+ "`nState`=?,dLoginDate=?,nLoginTimes=? where nid=?";

		Connection conn = null;
		try {

			conn = dataSource.getConnection();

			Object[] objs = new Object[] { user.getsEMail(),user.getsNickName(),user.getsPassword(),user.getnState(),user.getdLoginDate(),user.getnLoginTimes(),user.getnID() };
			this.update(conn, sql, objs);

			DbUtils.close(conn);
		} catch (SQLException e) {
			DbUtils.close(conn);
			e.printStackTrace();
		}
	}
}
